package com.hexiang.jdbc;

import com.hexiang.dao.OmsOrderDao;
import com.hexiang.domain.OmsOrder;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import javax.sql.DataSource;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 基于JDBC的daoimpl
 */
@Repository
public class OmsOrderJdbcDaoImpl implements OmsOrderDao {

    @Resource
    private DataSource dataSource;

    protected Connection getConn() throws SQLException {
        return dataSource.getConnection();
    }

    protected void closeConn(Connection conn) throws SQLException {
        if (conn != null) {
            conn.close();
        }
    }

    /**
     * 保存订单信息
     *
     * @param omsOrder
     * @return
     */
    public Long saveOmsOrder(OmsOrder omsOrder) {
        Long pkId = null;
        Connection conn = null;
        try {
            conn = this.getConn();
            PreparedStatement ps = conn.prepareStatement("INSERT INTO t_oms_order (member_id, order_sn, create_time, member_username, total_amount, pay_amount, freight_amount, pay_type, source_type, status, order_type) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);");
            ps.setLong(1, omsOrder.getMemberId());
            ps.setString(2, omsOrder.getOrderSn());
            ps.setDate(3, new java.sql.Date(omsOrder.getCreateTime().getTime()));
            ps.setString(4, omsOrder.getMemberUsername());
            ps.setBigDecimal(5, omsOrder.getTotalAmount());
            ps.setBigDecimal(6, omsOrder.getPayAmount());
            ps.setBigDecimal(7, omsOrder.getFreightAmount());
            ps.setInt(8, omsOrder.getPayType());
            ps.setInt(9, omsOrder.getSourceType());
            ps.setInt(10, omsOrder.getStatus());
            ps.setInt(11, omsOrder.getOrderType());
            ps.executeUpdate();
            // 检索由于执行此 Statement 对象而创建的所有自动生成的键 
            ResultSet rs = ps.getGeneratedKeys();
            if (rs.next()) {
                pkId = rs.getLong(1);
            }
            ps.close();
            conn.commit();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                this.closeConn(conn);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return pkId;
    }

    public List<Long> batchSaveOmsOrder(List<OmsOrder> omsOrderList) {
        List<Long> pkIdList = null;
        Connection conn = null;
        try {

            conn = this.getConn();
            String prefix = "INSERT INTO t_oms_order (member_id, order_sn, create_time, member_username, total_amount, pay_amount, freight_amount, pay_type, source_type, status, order_type) VALUES  ";
            // 设置事务为非自动提交
            StringBuilder suffix = null;
            // 比起st，pst会更好些
            conn.setAutoCommit(false);
            suffix = new StringBuilder();
            for (int j = 0; j < omsOrderList.size(); j++) {
                suffix.append("(");
                suffix.append("'" + omsOrderList.get(j).getMemberId() + "',");
                suffix.append("'" + omsOrderList.get(j).getOrderSn() + "',");
                suffix.append("'" + new java.sql.Date(omsOrderList.get(j).getCreateTime().getTime()) + "',");
                suffix.append("'" + omsOrderList.get(j).getMemberUsername() + "',");
                suffix.append("'" + omsOrderList.get(j).getTotalAmount() + "',");
                suffix.append("'" + omsOrderList.get(j).getPayAmount() + "',");
                suffix.append("'" + omsOrderList.get(j).getFreightAmount() + "',");
                suffix.append("'" + omsOrderList.get(j).getPayType() + "',");
                suffix.append("'" + omsOrderList.get(j).getSourceType() + "',");
                suffix.append("'" + omsOrderList.get(j).getStatus() + "',");
                suffix.append("'" + omsOrderList.get(j).getOrderType() + "' ");
                suffix.append("),");

            }
            // 添加执行SQL
            String sql = prefix + suffix.substring(0, suffix.length() - 1);

            Statement ps = conn.createStatement();
            ps.addBatch(sql);
            ps.executeBatch();
            // 检索由于执行此 Statement 对象而创建的所有自动生成的键
            ResultSet rs = ps.getGeneratedKeys();
            if (rs.next()) {
                Long pkId = rs.getLong(1);
                pkIdList.add(pkId);
            }
            ps.close();
            conn.commit();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                this.closeConn(conn);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return pkIdList;
    }


    /**
     * 查询所有订单信息
     *
     * @return
     */
    public List<OmsOrder> queryAllOmsOrder() {
        List<OmsOrder> list = new ArrayList<>();

        Connection conn = null;
        try {
            conn = this.getConn();
            StringBuilder sql = new StringBuilder("select * from t_oms_order ; ");

            PreparedStatement ps = conn.prepareStatement(sql.toString());
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                OmsOrder omsOrder = new OmsOrder();
                omsOrder.setId(rs.getLong("id"));
                omsOrder.setMemberId(rs.getLong("member_id"));
                omsOrder.setOrderSn(rs.getString("order_sn"));
                omsOrder.setCreateTime(rs.getDate("create_time"));
                omsOrder.setMemberUsername(rs.getString("member_username"));
                omsOrder.setTotalAmount(rs.getBigDecimal("total_amount"));
                omsOrder.setPayAmount(rs.getBigDecimal("pay_amount"));
                omsOrder.setFreightAmount(rs.getBigDecimal("freight_amount"));
                omsOrder.setPayType(rs.getInt("pay_type"));
                omsOrder.setSourceType(rs.getInt("source_type"));
                omsOrder.setStatus(rs.getInt("status"));
                omsOrder.setOrderType(rs.getInt("order_type"));
                omsOrder.setModifyTime(rs.getDate("modify_time"));
                list.add(omsOrder);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                this.closeConn(conn);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return list;
    }


    @Override
    public int[][] batchSave(List<OmsOrder> omsOrderList) {
        int[][] pkIdList = null;
        Connection conn = null;
        try {

            conn = this.getConn();
            String prefix = "INSERT INTO t_oms_order (member_id, order_sn, create_time, member_username, total_amount, pay_amount, freight_amount, pay_type, source_type, status, order_type) VALUES  ";
            // 设置事务为非自动提交
            StringBuilder suffix = null;
            // 比起st，pst会更好些
            conn.setAutoCommit(false);
            suffix = new StringBuilder();
            for (int j = 0; j < omsOrderList.size(); j++) {
                suffix.append("(");
                suffix.append("'" + omsOrderList.get(j).getMemberId() + "',");
                suffix.append("'" + omsOrderList.get(j).getOrderSn() + "',");
                suffix.append("'" + new java.sql.Date(omsOrderList.get(j).getCreateTime().getTime()) + "',");
                suffix.append("'" + omsOrderList.get(j).getMemberUsername() + "',");
                suffix.append("'" + omsOrderList.get(j).getTotalAmount() + "',");
                suffix.append("'" + omsOrderList.get(j).getPayAmount() + "',");
                suffix.append("'" + omsOrderList.get(j).getFreightAmount() + "',");
                suffix.append("'" + omsOrderList.get(j).getPayType() + "',");
                suffix.append("'" + omsOrderList.get(j).getSourceType() + "',");
                suffix.append("'" + omsOrderList.get(j).getStatus() + "',");
                suffix.append("'" + omsOrderList.get(j).getOrderType() + "' ");
                suffix.append("),");

            }
            // 添加执行SQL
            String sql = prefix + suffix.substring(0, suffix.length() - 1);

            Statement ps = conn.createStatement();
            ps.addBatch(sql);

            ps.executeBatch();
            ps.close();
            conn.commit();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                this.closeConn(conn);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return pkIdList;
    }

    @Override
    public int[][] batchUpdate(List<OmsOrder> tist) {
        return new int[0][];
    }

    @Override
    public Long save(OmsOrder omsOrder) {
        return null;
    }

    @Override
    public OmsOrder get(long id) {
        return null;
    }

    @Override
    public Integer deleteById(long id) {
        return null;
    }

    @Override
    public Integer update(OmsOrder omsOrder) {
        return null;
    }


    @Override
    public List<OmsOrder> queryAll() {
        List<OmsOrder> list = new ArrayList<>();

        Connection conn = null;
        try {
            conn = this.getConn();
            StringBuilder sql = new StringBuilder("select * from t_oms_order ; ");

            PreparedStatement ps = conn.prepareStatement(sql.toString());
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                OmsOrder omsOrder = new OmsOrder();
                omsOrder.setId(rs.getLong("id"));
                omsOrder.setMemberId(rs.getLong("member_id"));
                omsOrder.setOrderSn(rs.getString("order_sn"));
                omsOrder.setCreateTime(rs.getDate("create_time"));
                omsOrder.setMemberUsername(rs.getString("member_username"));
                omsOrder.setTotalAmount(rs.getBigDecimal("total_amount"));
                omsOrder.setPayAmount(rs.getBigDecimal("pay_amount"));
                omsOrder.setFreightAmount(rs.getBigDecimal("freight_amount"));
                omsOrder.setPayType(rs.getInt("pay_type"));
                omsOrder.setSourceType(rs.getInt("source_type"));
                omsOrder.setStatus(rs.getInt("status"));
                omsOrder.setOrderType(rs.getInt("order_type"));
                omsOrder.setModifyTime(rs.getDate("modify_time"));
                list.add(omsOrder);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                this.closeConn(conn);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return list;
    }

    @Override
    public List<OmsOrder> queryListByLimit(int limit) {
        List<OmsOrder> list = new ArrayList<>();

        Connection conn = null;
        try {
            conn = this.getConn();
            StringBuilder sql = new StringBuilder("select * from t_oms_order limit " + limit + ";");

            PreparedStatement ps = conn.prepareStatement(sql.toString());
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                OmsOrder omsOrder = new OmsOrder();
                omsOrder.setId(rs.getLong("id"));
                omsOrder.setMemberId(rs.getLong("member_id"));
                omsOrder.setOrderSn(rs.getString("order_sn"));
                omsOrder.setCreateTime(rs.getDate("create_time"));
                omsOrder.setMemberUsername(rs.getString("member_username"));
                omsOrder.setTotalAmount(rs.getBigDecimal("total_amount"));
                omsOrder.setPayAmount(rs.getBigDecimal("pay_amount"));
                omsOrder.setFreightAmount(rs.getBigDecimal("freight_amount"));
                omsOrder.setPayType(rs.getInt("pay_type"));
                omsOrder.setSourceType(rs.getInt("source_type"));
                omsOrder.setStatus(rs.getInt("status"));
                omsOrder.setOrderType(rs.getInt("order_type"));
                omsOrder.setModifyTime(rs.getDate("modify_time"));
                list.add(omsOrder);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                this.closeConn(conn);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return list;
    }

    @Override
    public Integer count() {
        return null;
    }
}
